package com.eastrobot.report.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.eastrobot.report.model.Field;
import com.eastrobot.report.model.Table;

/**
 * 数据元数据
 * 
 * @author baoy
 * 
 */
public class DBdomain {

	private final Logger logger = LoggerFactory.getLogger(DBdomain.class);

	private DatabaseMetaData dbMetaData;

	private static DBdomain domain = new DBdomain();

	public static DBdomain getInstance() { return domain; }

	private DBdomain() { init(); }

	public void init() {
		Connection conn = DBconnection.getInstance().getConnection();
		try {
			dbMetaData = conn.getMetaData();
		} catch (SQLException e) {
			logger.error("获取元数据出错", e);
			throw new RuntimeException(e);
		} finally {
			close(conn);
		}
	}

	// 过滤收回站的表
	private boolean ignoreTables(String tableName) {
		boolean ignore = true;
		try {
			if ("Oracle".equalsIgnoreCase(dbMetaData.getDatabaseProductName())
					&& tableName.startsWith("BIN$")) {
				ignore = false;
			}
		} catch (SQLException e) {
			logger.error("获取元数据出错", e);
			throw new RuntimeException(e);
		}
		return ignore;
	}

	// 获取数据库的所有表名称
	public List<String> getTableNames() {
		List<String> list = new ArrayList<String>();
		ResultSet rs = null;
		try {
			rs = dbMetaData.getTables(null, DBconnection.getInstance().jdbcProp
					.get("jdbc.username").toUpperCase(), "%", new String[] { "TABLE" });
			while (rs.next()) {
				String tableName = rs.getString(3);
				if (ignoreTables(tableName)) list.add(tableName);
			}
		} catch (SQLException e) {
			logger.error("获取数据表出错", e);
			throw new RuntimeException(e);
		} finally {
			close(rs);
		}
		return list;
	}

	// 根据表名称，获取该表的所有字段
	public List<Field> getFields(String tableName) {
		List<Field> fields = new ArrayList<Field>();
		ResultSet rs = null;
		try {
			rs = dbMetaData.getColumns(null, "%", tableName, "%");
			while (rs.next()) {
				Field f = new Field(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"),
						rs.getString("REMARKS"), rs.getInt("COLUMN_SIZE"));
				fields.add(f);
			}
		} catch (SQLException e) {
			logger.error("获取数据表字段出错", e);
			throw new RuntimeException(e);
		} finally {
			close(rs);
		}
		return fields;
	}

	// 获取数据表和表字段信息
	public List<Table> getTableInfo() {
		List<Table> tbs = new ArrayList<Table>();
		for (String tableName : getTableNames()) {
			Table tb = new Table();
			tb.setName(tableName);
			tb.setFields(getFields(tableName));
			tbs.add(tb);
		}
		return tbs;
	}

	// 关闭数据库连接
	private void close(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("关闭数据库连接出错！", e);
				throw new RuntimeException(e);
			}
		}
	}

	// 关闭数据游标
	private void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				logger.error("关闭数据集出错！", e);
				throw new RuntimeException(e);
			}
		}
	}

	public static void main(String[] args) {
		List<Table> tbs = DBdomain.getInstance().getTableInfo();
		for (int i = 0; i < tbs.size(); i++) {
			Table tb = tbs.get(i);
			if (tb.getName().equals("AUTH_USER")) {
				for (Field f : tb.getFields()) {
					System.out.println(f.getName());
				}
			}
		}
		
//		List<String> tableNames = DBdomain.getInstance().getTableNames();
//		for (String name : tableNames) {
//			System.out.println(name);
//		}
	}

}
